USE MASTER
GO
ALTER DATABASE BetBattles
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE BetBattles
GO

CREATE DATABASE BetBattles;
GO

CREATE TABLE [BetBattles].[dbo].[Roles](
	[RoleId] INT NOT NULL IDENTITY(1,1),
	[RoleName] NVARCHAR(20) NOT NULL,
	CONSTRAINT pk_RoleId primary key(RoleId)
);

CREATE TABLE [BetBattles].[dbo].[Users](
	[UserId] INT NOT NULL IDENTITY(1,1),
	[UserRoleId] INT NULL,
	[Login] NVARCHAR(20) NOT NULL,
	[PasswordHash] NVARCHAR(32) NOT NULL,
	[PasswordSault] NVARCHAR(5) NULL,
	[E-Mail] NVARCHAR(30) NOT NULL,
	[RegistrationDate] DATETIME NOT NULL,
	CONSTRAINT pk_IserId PRIMARY KEY(UserId),
	CONSTRAINT fk_UserRole FOREIGN KEY (UserRoleId) REFERENCES [Roles](RoleId)
);

GO
CREATE TABLE [BetBattles].[dbo].[Results](
	[ResultId] INT NOT NULL IDENTITY(1,1),
	[Score1] INT NOT NULL,
	[Score2] INT NOT NULL,
	CONSTRAINT pk_ResultId PRIMARY KEY(ResultId),
);

GO
CREATE TABLE [BetBattles].[dbo].[MatchTypes](
	[MatchTypeId] INT NOT NULL IDENTITY(1,1),
	[Description] NVARCHAR(50) NOT NULL,
	CONSTRAINT pk_MatchTypeId PRIMARY KEY(MatchTypeId),
);

GO
CREATE TABLE [BetBattles].[dbo].[Matches](
	[MatchId] INT NOT NULL IDENTITY(1,1),
	[MatchTypeId] INT NULL,
	[ResultId] INT NULL,
	[Command1] NVARCHAR(35) NOT NULL,
	[Command2] NVARCHAR(35) NOT NULL,
	[Date] DATETIME NOT NULL,
	CONSTRAINT pk_MatchId PRIMARY KEY(MatchId),
	CONSTRAINT fk_MatchMatchType FOREIGN KEY (MatchTypeId) REFERENCES [MatchTypes](MatchTypeId),
	CONSTRAINT fk_MatchResult FOREIGN KEY (ResultId) REFERENCES [Results](ResultId)
);

GO
CREATE TABLE [BetBattles].[dbo].[Bets](
	[BetId] INT NOT NULL IDENTITY(1,1),
	[UserId] INT NOT NULL,
	[MatchId] INT NOT NULL,
	[ResultId] INT NOT NULL,
	[IsReleased] BIT NOT NULL,
	CONSTRAINT pk_BetId PRIMARY KEY(BetId),
	CONSTRAINT fk_BetUser FOREIGN KEY (UserId) REFERENCES [Users](UserId),
	CONSTRAINT fk_BetMatch FOREIGN KEY (MatchId) REFERENCES [Matches](MatchId),
	CONSTRAINT fk_BetResult FOREIGN KEY (ResultId) REFERENCES [Results](ResultId)
);

GO
CREATE TABLE [BetBattles].[dbo].[Groups](
	[GroupId] INT NOT NULL IDENTITY(1,1),
	[Name] NVARCHAR(30) NULL,
	[Description] NVARCHAR(300) NULL,
	[Start] DATETIME NULL,
	[Finish] DATETIME NULL,
	CONSTRAINT pk_GroupId PRIMARY KEY(GroupId),
);

GO
CREATE TABLE [BetBattles].[dbo].[GroupUserTickets](
	[GroupUserTicketId] INT NOT NULL IDENTITY(1,1),
	[GroupId] INT NOT NULL,
	[UserId] INT NOT NULL,
	[UserRateInGroup] INT NOT NULL,
	CONSTRAINT pk_GroupUserTicketId PRIMARY KEY(GroupUserTicketId),
	CONSTRAINT fk_guGroup FOREIGN KEY (GroupId) REFERENCES [Groups](GroupId),
	CONSTRAINT fk_guUser FOREIGN KEY (UserId) REFERENCES [Users](UserId)
);

GO
create table [BetBattles].[dbo].[GroupMatchTickets](
	[GroupMatchTicketId] INT NOT NULL IDENTITY(1,1),
	[GroupId] INT NOT NULL,
	[MatchId] INT NOT NULL,
	CONSTRAINT pk_GroupMatchTicketId PRIMARY KEY(GroupMatchTicketId),
	CONSTRAINT fk_gmGroup FOREIGN KEY (GroupId) REFERENCES [Groups](GroupId),
	CONSTRAINT fk_gmMatch FOREIGN KEY (MatchId) REFERENCES [Matches](MatchId)
);

GO
CREATE TABLE [BetBattles].[dbo].[GroupMatchTypeTickets](
	[GroupMatchTypeTicketId] INT NOT NULL IDENTITY(1,1),
	[GroupId] INT NOT NULL,
	[MatchTypeId] INT NOT NULL,
	CONSTRAINT pk_CroupMatchTypeTicketId PRIMARY KEY(GroupMatchTypeTicketId),
	CONSTRAINT fk_gmtGroup FOREIGN KEY (GroupId) REFERENCES [Groups](GroupId),
	CONSTRAINT fk_gmtMatchType FOREIGN KEY (MatchTypeId) REFERENCES [MatchTypes](MatchTypeId)
);

GO
ALTER DATABASE BetBattles
SET MULTI_USER
GO